﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Project.T4Code
{
    /// <summary>
    /// Inset语句类
    /// </summary>
    public class InsertSqlBuilder : BaseSqlBuilder
    {
        public InsertSqlBuilder(string tableName, PropertyInfo[] properties, string provider)
            : base(tableName, properties, provider)
        {
        }
        /// <summary>
        /// 获取Insert语句
        /// </summary>
        /// <returns></returns>
        public override string GetSqlStr()
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0, j = Properties.Length; i < j; i++)
            {
                var attribute = Properties[i].GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                //有属性标签
                if (attribute != null)
                {
                    //直接调用方法将参数生成出来放到sb中
                    sb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name), attribute.PrimaryKey));
                }
                else//没有标签
                {
                    sb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name)));
                }

            }
            string str = sb.ToString();
            str = str.Substring(0, str.LastIndexOf(',') >= str.Length - 2 ? str.LastIndexOf(',') : str.Length);
            string result = string.Format("insert into {0}({1}) values({2})", TableName, str.Replace("@", ""), str);
            if (ProviderFactoryString == "System.Data.OracleClient")
            {
                result = result.Replace("@", ":");
                // result += "select userinfo.currval from dual";
            }
            else
            {
                result += ";SELECT CAST(SCOPE_IDENTITY() as int)";
            }

            return result;
        }
        /// <summary>
        /// 获取相应的字符串
        /// </summary>
        /// <param name="propertyName">属性参数</param>
        /// <param name="tableName">表名，查询的时候需要用到</param>
        /// <param name="nowLength">循环到的第几个参数</param>
        /// <param name="length">总的参数个数</param>
        /// <param name="isKey">是否为主键，默认不是主键</param>
        /// <param name="isContent">是否含有包含类</param>
        /// <param name="isEnum">是否是枚举</param>
        /// <returns></returns>
        public override string GetStringFirst(string propertyName, string tableName, int nowLength, int length, bool isContent = false, bool isKey = false, bool isEnum = false)
        {
            string result = "";
            //insert时 主键不需要当作参数传进来
            if (!isKey)
            {
                if (!isContent)
                {
                    result = string.Format("@{0}, ", propertyName);
                }
            }
            return result;
        }
    }
    /// <summary>
    /// 拼接Edit语句类
    /// </summary>
    public class EditSqlBuilder : BaseSqlBuilder
    {
        public EditSqlBuilder(string tableName, PropertyInfo[] properties, string provider)
            : base(tableName, properties, provider)
        {

        }
        /// <summary>
        /// 获取修改语句
        /// </summary>
        /// <returns></returns>
        public override string GetSqlStr()
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder keySb = new StringBuilder();
            for (int i = 0, j = Properties.Length; i < j; i++)
            {
                var attribute = Properties[i].GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                //有标签
                if (attribute != null)
                {
                    //主键要放到单独的sb中
                    if (attribute.PrimaryKey)
                    {
                        keySb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name), attribute.PrimaryKey));
                    }
                    else
                    {
                        sb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name), attribute.PrimaryKey));
                    }

                }
                else//没有标签
                {
                    sb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name)));
                }

            }
            string str = sb.ToString();
            str = str.Substring(0, str.LastIndexOf(',') >= str.Length - 2 ? str.LastIndexOf(',') : str.Length);
            string result = string.Format("update {0} set {1} where {2}", TableName, str, keySb);

            if (ProviderFactoryString == "System.Data.OracleClient")
            {
                result = result.Replace("@", ":");
            }
            return result;
        }
        /// <summary>
        /// 获取相应的字符串
        /// </summary>
        /// <param name="propertyName">属性参数</param>
        /// <param name="tableName">表名，查询的时候需要用到</param>
        /// <param name="nowLength">循环到的第几个参数</param>
        /// <param name="length">总的参数个数</param>
        /// <param name="isKey">是否为主键，默认不是主键</param>
        /// <param name="isContent">是否含有包含类</param>
        /// <param name="isEnum">是否是枚举</param>
        /// <returns></returns>
        public override string GetStringFirst(string propertyName, string tableName, int nowLength, int length, bool isContent = false, bool isKey = false, bool isEnum = false)
        {
            string result = "";
            if (!isContent)
            {
                //update时 主键作为条件
                if (!isKey)
                {
                    result = string.Format("{0} = @{0}", propertyName) + ", ";
                }
                else
                {
                    result = string.Format("{0} = @{0}", propertyName);
                }
            }
            return result;
        }
    }
    /// <summary>
    /// 拼接Delete语句类
    /// </summary>
    public class DeleteSqlBuilder : BaseSqlBuilder
    {
        public DeleteSqlBuilder(string tableName, PropertyInfo[] properties, string provider)
            : base(tableName, properties, provider)
        {

        }

        /// <summary>
        /// 获取删除语句
        /// </summary>
        /// <returns></returns>
        public override string GetSqlStr()
        {
            StringBuilder sb = new StringBuilder();
            int j = 0;
            for (int i = 0; i < Properties.Length; i++)
            {
                var attribute = Properties[i].GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                //删除的时候只需要主键
                if (attribute.PrimaryKey)
                {
                    sb.Append(string.Format("{0} = @{0}", Properties[i].Name));
                    break;
                }
                else
                {
                    j++;
                }
            }
            if (j == Properties.Length)
            {
                throw new Exception("表必须有主键!");
            }
            string result = string.Format("delete {0} where {1} ", TableName, sb.ToString());
            if (ProviderFactoryString == "System.Data.OracleClient")
            {
                result = result.Replace("@", ":");
            }
            return result;
        }
        /// <summary>
        /// 获取相应的字符串
        /// </summary>
        /// <param name="propertyName">属性参数</param>
        /// <param name="tableName">表名，查询的时候需要用到</param>
        /// <param name="nowLength">循环到的第几个参数</param>
        /// <param name="length">总的参数个数</param>
        /// <param name="isKey">是否为主键，默认不是主键</param>
        /// <param name="isContent">是否含有包含类</param>
        /// <param name="isEnum">是否是枚举</param>
        /// <returns></returns>
        public override string GetStringFirst(string propertyName, string tableName, int nowLength, int length, bool isContent = false, bool isKey = false, bool isEnum = false)
        {
            return "";
        }
    }
    /// <summary>
    /// 拼接查询语句类
    /// </summary>
    public class QuerySqlBuilder : BaseSqlBuilder
    {
        public QuerySqlBuilder(string tableName, PropertyInfo[] properties, string provider)
            : base(tableName, properties, provider)
        {

        }
        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <returns></returns>
        public override string GetSqlStr()
        {
            //拼接sql语句
            StringBuilder baseSb = new StringBuilder();
            //保存包含类 key为包含类的类名 value为包含类的主键
            Dictionary<string, string> contentDict = new Dictionary<string, string>();
            //保存包含类名及属性字符串
            Dictionary<string, StringBuilder> contentSbDict = new Dictionary<string, StringBuilder>();
            for (int i = 0, j = Properties.Length; i < j; i++)
            {
                //有包含类的时候将包含类也关联查询出来
                if (tableDict.Keys.Contains(Properties[i].Name))
                {
                    //属性字符串
                    StringBuilder contentSb = new StringBuilder();
                    var contentProperties = tableDict[Properties[i].Name].GetProperties();
                    //遍历包含类属性
                    for (int m = 0, n = contentProperties.Length; m < n; m++)
                    {
                        //获取属性的标签
                        var attribute = contentProperties[m].GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                        //判断主键
                        if (attribute != null && attribute.PrimaryKey)
                        {
                            //将包含类名和主键放到字典中
                            contentDict.Add(Properties[i].Name, contentProperties[m].Name);
                        }
                        contentSb.Append(GetStringFirst(contentProperties[m].Name, Properties[i].Name, m, n,
                                                        tableDict.Keys.Contains(contentProperties[m].Name), false,
                                                        enumList.Contains(contentProperties[m].Name)));
                    }
                    contentSbDict.Add(Properties[i].Name, contentSb);
                }
                else
                {
                    //主类属性字符串放到相应的集合中
                    baseSb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name), false, enumList.Contains(Properties[i].Name)));
                }
            }
            //查询语句例子，采用left join
            //select u.Id,u.UserName,u.Age,CAST(u.Gender as int) Gender,u.Address,u.CreateTime,u.Phone,u.EmployeeId,e.Id,e.LoginName,e.LoginPwd from UserInfo u left join Employee e on u.EmployeeId=e.Id

            StringBuilder sb = new StringBuilder();
            //主类的第一个字母
            string tableLowerFirst = TableName.ToLower().Substring(0, 1);
            string baseStr = baseSb.ToString();
            if (baseStr.LastIndexOf(',') == baseStr.Length - 1)
            {
                baseStr = baseStr.Substring(0, baseStr.Length - 1);
            }
            sb.Append(string.Format("select {0} ", baseStr));
            //遍历包含类的属性字符串，放到sql语句中
            foreach (var contentTableName in contentSbDict.Keys)
            {
                sb.Append(string.Format(",{0}", contentSbDict[contentTableName]));
            }
            sb.Append(string.Format(" from {0} {1}", TableName, tableLowerFirst));
            //遍历包含类的主键 拼接join语句
            foreach (var contentTableName in contentDict.Keys)
            {
                //包含类的第一个字母
                string contentTableNameLowerFirst = contentTableName.ToLower().Substring(0, 1);
                sb.Append(string.Format(" left join {0} {1} on {2}.{3} = {4}.{5} ", contentTableName, contentTableNameLowerFirst, tableLowerFirst, contentTableName + "Id", contentTableNameLowerFirst, contentDict[contentTableName]));
            }

            return sb.ToString();
        }
        /// <summary>
        /// 获取相应的字符串
        /// </summary>
        /// <param name="propertyName">属性参数</param>
        /// <param name="tableName">表名，查询的时候需要用到</param>
        /// <param name="nowLength">循环到的第几个参数</param>
        /// <param name="length">总的参数个数</param>
        /// <param name="isKey">是否为主键，默认不是主键</param>
        /// <param name="isContent">是否含有包含类</param>
        /// <param name="isEnum">是否是枚举</param>
        /// <returns></returns>
        public override string GetStringFirst(string propertyName, string tableName, int nowLength, int length, bool isContent = false, bool isKey = false, bool isEnum = false)
        {
            string result = "";
            //列名，当是枚举列的时候需要特殊处理
            string pName = tableName.Substring(0, 1).ToLower() + "." + propertyName;
            //查询的时候需要把所有的都查询出来，所有的都放到参数里面，不需要判断主键
            if (nowLength == length - 1)
            {
                if (isEnum)
                {
                    result = GetEnumString("CAST({0} as int) as {1}", pName, propertyName);
                }
                else
                {
                    if (!isContent)
                    {
                        result = string.Format("{0}", pName);
                    }

                }
            }
            else
            {
                if (isEnum)
                {
                    result = GetEnumString("CAST({0} as int) as {1},", pName, propertyName);
                }
                else
                {
                    if (!isContent)
                    {
                        result = string.Format("{0},", pName);
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 获取枚举字符串
        /// </summary>
        /// <param name="model">字符串模版</param>
        /// <param name="propertyName1">第一个参数</param>
        /// <param name="propertyName2">第二个参数</param>
        /// <returns></returns>
        public string GetEnumString(string model, string propertyName1, string propertyName2)
        {
            return string.Format(model, propertyName1, propertyName2);
        }



        /// <summary>
        /// 获取相应的所有包含的子类
        /// </summary>
        /// <param name="properties"></param>
        /// <returns></returns>
        public List<string> GetAllContentTable(PropertyInfo[] properties)
        {
            List<string> contentList = new List<string>();
            foreach (var property in properties)
            {
                if (tableDict.Keys.Contains(property.PropertyType.Name))
                {
                    contentList.Add(property.PropertyType.Name);
                }
            }
            return contentList;
        }


        /// <summary>
        /// 联查的时候需要特殊处理query条件
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="properties">属性名</param>
        /// <returns></returns>
        public override string GetConQueryStr(string tableName, PropertyInfo[] properties)
        {
            string result = "";
            string tableLowerFirst = tableName.ToLower().Substring(0, 1);
            var contentList = GetAllContentTable(properties);
            if (contentList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sb1 = new StringBuilder();
                StringBuilder sb2 = new StringBuilder();
                StringBuilder sb3 = new StringBuilder();
                sb1.Append(tableName + ", ");
                sb2.Append(tableLowerFirst);
                for (int i = 0; i < contentList.Count; i++)
                {
                    //var userList = connection.Query<UserInfo, Employee, UserInfo>(sql, (u, e) => { u.Employee = e; return u; });
                    string contentLowerFirst = contentList[i].ToLower().Substring(0, 1);
                    sb1.Append(contentList[i] + ", ");
                    sb2.Append(", " + contentLowerFirst);
                    sb3.Append(string.Format("{0}.{1} = {2};", tableLowerFirst, contentList[i], contentLowerFirst));
                }
                sb1.Append(tableName);
                sb3.Append(string.Format("return {0};", tableLowerFirst));

                result = string.Format("connection.Query<{0}>(sql, ({1}) => {{{2}}})", sb1, sb2, sb3);
            }
            else
            {
                result = string.Format("connection.Query<{0}>(sql)", tableName);
            }
            return result;
        }
    }
    /// <summary>
    /// 这里准备写分页查询
    /// </summary>
    public class QuerySqlPagerBuilder : BaseSqlBuilder
    {
        public QuerySqlPagerBuilder(string tableName, PropertyInfo[] properties, string provider)
            : base(tableName, properties, provider)
        {

        }

        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <returns></returns>
        public override string GetSqlStr()
        {
            //todo: 拼接分页sql语句
            //查询最外层的sql语句
            StringBuilder outside = new StringBuilder();

            //拼接sql语句
            StringBuilder baseSb = new StringBuilder();
            //保存包含类 key为包含类的类名 value为包含类的主键
            Dictionary<string, string> contentDict = new Dictionary<string, string>();
            //保存包含类名及属性字符串
            Dictionary<string, StringBuilder> contentSbDict = new Dictionary<string, StringBuilder>();
            for (int i = 0, j = Properties.Length; i < j; i++)
            {
                //有包含类的时候将包含类也关联查询出来
                if (tableDict.Keys.Contains(Properties[i].Name))
                {
                    //属性字符串
                    StringBuilder contentSb = new StringBuilder();
                    var contentProperties = tableDict[Properties[i].Name].GetProperties();
                    //遍历包含类属性
                    for (int m = 0, n = contentProperties.Length; m < n; m++)
                    {
                        //获取属性的标签
                        var attribute = contentProperties[m].GetCustomAttributes(typeof(DataObjectFieldAttribute)).FirstOrDefault() as DataObjectFieldAttribute;
                        //判断主键
                        if (attribute != null && attribute.PrimaryKey)
                        {
                            //将包含类名和主键放到字典中
                            contentDict.Add(Properties[i].Name, contentProperties[m].Name);
                            var contentId = Properties[i].Name.Substring(0, 1) + "Id";
                            outside.Append(contentProperties[m].Name);
                            outside.Append(" ");
                            outside.Append(contentId);
                            outside.Append(", ");
                        }
                        else
                        {
                            outside.Append(contentProperties[m].Name);
                            outside.Append(", ");
                        }
                        contentSb.Append(GetStringFirst(contentProperties[m].Name, Properties[i].Name, m, n,
                                                        tableDict.Keys.Contains(contentProperties[m].Name), false,
                                                        enumList.Contains(contentProperties[m].Name)));
                    }
                    contentSbDict.Add(Properties[i].Name, contentSb);
                }
                else
                {
                    //去除 包含类的名称  包含类的主键
                    if (tableDict.Keys.Contains(Properties[i].Name) || tableDict.Keys.Contains(Properties[i].Name.Replace("Id", "")))
                    {

                    }
                    else
                    {
                        //主类属性字符串放到相应的集合中
                        baseSb.Append(GetStringFirst(Properties[i].Name, TableName, i, j, tableDict.Keys.Contains(Properties[i].Name), false, enumList.Contains(Properties[i].Name)));
                        outside.Append(Properties[i].Name);
                        outside.Append(", ");
                    }
                }
            }
            StringBuilder sb = new StringBuilder();
            //主类的第一个字母
            string tableLowerFirst = TableName.ToLower().Substring(0, 1);
            string baseStr = baseSb.ToString();
            while (baseStr.LastIndexOf(',') >= baseStr.Length - 1)
            {
                baseStr = baseStr.Substring(0, baseStr.Length - 1);
            }
            string outsideStr = outside.ToString().Trim(' ');
            while (outsideStr.LastIndexOf(',') >= outsideStr.Length - 1)
            {
                outsideStr = outsideStr.Substring(0, outsideStr.Length - 1);
            }
            sb.Append(string.Format("select {0} from (", outsideStr));
            sb.Append(string.Format("select row_number() over(order by u.UserInfoId desc) as rk, {0} ", baseStr));
            //遍历包含类的属性字符串，放到sql语句中
            foreach (var contentTableName in contentSbDict.Keys)
            {
                sb.Append(string.Format(", {0}", contentSbDict[contentTableName]));
            }
            sb.Append(string.Format(" from {0} {1}", TableName, tableLowerFirst));
            //遍历包含类的主键 拼接join语句
            foreach (var contentTableName in contentDict.Keys)
            {
                //包含类的第一个字母
                string contentTableNameLowerFirst = contentTableName.ToLower().Substring(0, 1);
                sb.Append(string.Format(" left join {0} {1} on {2}.{3} = {4}.{5} ", contentTableName, contentTableNameLowerFirst, tableLowerFirst, contentTableName + "Id", contentTableNameLowerFirst, contentDict[contentTableName]));
            }
            sb.Append(string.Format(") {0} where rk between {{0}} and {{1}}  ", tableLowerFirst));

            //string result = sb.ToString();
            //if (ProviderFactoryString == "System.Data.OracleClient")
            //{
            //    result = result.Replace("@", ":");
            //}
            //return result;
            return sb.ToString();
        }

        /// <summary>
        /// 获取相应的字符串
        /// </summary>
        /// <param name="propertyName">属性参数</param>
        /// <param name="tableName">表名，查询的时候需要用到</param>
        /// <param name="nowLength">循环到的第几个参数</param>
        /// <param name="length">总的参数个数</param>
        /// <param name="isKey">是否为主键，默认不是主键</param>
        /// <param name="isContent">是否含有包含类</param>
        /// <param name="isEnum">是否是枚举</param>
        /// <returns></returns>
        public override string GetStringFirst(string propertyName, string tableName, int nowLength, int length, bool isContent = false, bool isKey = false, bool isEnum = false)
        {
            string result = "";
            //列名，当是枚举列的时候需要特殊处理
            string pName = tableName.Substring(0, 1).ToLower() + "." + propertyName;
            //查询的时候需要把所有的都查询出来，所有的都放到参数里面，不需要判断主键
            if (nowLength == length - 1)
            {
                if (isEnum)
                {
                    result = GetEnumString("CAST({0} as int) as {1}", pName, propertyName);
                }
                else
                {
                    if (!isContent)
                    {
                        result = string.Format("{0}", pName);
                    }

                }
            }
            else
            {
                if (isEnum)
                {
                    result = GetEnumString("CAST({0} as int) as {1},", pName, propertyName);
                }
                else
                {
                    if (!isContent)
                    {
                        result = string.Format("{0},", pName);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 获取枚举字符串
        /// </summary>
        /// <param name="model">字符串模版</param>
        /// <param name="propertyName1">第一个参数</param>
        /// <param name="propertyName2">第二个参数</param>
        /// <returns></returns>
        public string GetEnumString(string model, string propertyName1, string propertyName2)
        {
            return string.Format(model, propertyName1, propertyName2);
        }



        /// <summary>
        /// 获取相应的所有包含的子类
        /// </summary>
        /// <param name="properties"></param>
        /// <returns></returns>
        public List<string> GetAllContentTable(PropertyInfo[] properties)
        {
            List<string> contentList = new List<string>();
            foreach (var property in properties)
            {
                if (tableDict.Keys.Contains(property.PropertyType.Name))
                {
                    contentList.Add(property.PropertyType.Name);
                }
            }
            return contentList;
        }

        /// <summary>
        /// 联查的时候需要特殊处理query条件
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="properties">属性名</param>
        /// <returns></returns>
        public override string GetConQueryStr(string tableName, PropertyInfo[] properties)
        {
            string result = "";
            string tableLowerFirst = tableName.ToLower().Substring(0, 1);
            var contentList = GetAllContentTable(properties);
            if (contentList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                StringBuilder sb1 = new StringBuilder();
                StringBuilder sb2 = new StringBuilder();
                StringBuilder sb3 = new StringBuilder();
                StringBuilder sb4 = new StringBuilder();
                sb1.Append(tableName + ", ");
                sb2.Append(tableLowerFirst);
                for (int i = 0; i < contentList.Count; i++)
                {
                    //var userList = connection.Query<UserInfo, Employee, UserInfo>(sql, (u, e) => { u.Employee = e; return u; });
                    string contentLowerFirst = contentList[i].ToLower().Substring(0, 1);
                    sb1.Append(contentList[i] + ", ");
                    sb2.Append(", " + contentLowerFirst);
                    sb3.Append(string.Format("{0}.{1} = {2};", tableLowerFirst, contentList[i], contentLowerFirst));
                    sb4.Append(contentList[i].Substring(0, 1) + "Id,");
                }
                sb1.Append(tableName);
                sb3.Append(string.Format("return {0};", tableLowerFirst));

                result = string.Format(@"connection.Query<{0}>(sql, ({1}) => {{{2}}}, splitOn: ""{3}"")", sb1, sb2, sb3, sb4.ToString().Substring(0, sb4.ToString().Length - 1));
            }
            else
            {
                result = string.Format("connection.Query<{0}>(sql)", tableName);
            }

            //var userList = connection.Query<UserInfo, Employee, ActionInfo, UserInfo>(sql, (u, e, a) => { u.Employee = e; u.ActionInfo = a; return u; }, new { pageIndex = pageIndex, pageCount = pageCount }, null, true, splitOn: "EId,AId");	

            return result;
        }

    }
}